import time
from contextlib import contextmanager
import json
#from psycopg2.pool import SimpleConnectionPool
import os
import requests
import json
import uuid
import hashlib
import time
import pymysql
from DBUtils.PooledDB import PooledDB, SharedDBConnection
from app.config import Config

def get_time():
    time_str = time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")

'''
PG_NAME='blog'
PG_USER='postgres'
PG_HOST="mysql.fzj.com"
PG_PW="FanYan879688"
PG_PORT="5432"
POOL = SimpleConnectionPool(1, 5, dbname=PG_NAME, user=PG_USER, host=PG_HOST, password=PG_PW, port=PG_PORT)
# 数据库链接
>>>>>>> 3a2b1190a74c448183f2b4b08a29fd52edd3cce0
@contextmanager
def get_cursor():
    conn = pool.connection()
    # 默认就自动提交
    conn.autocommit = True
    try:
        cur=conn.cursor()
        yield cur
        # 或设置手动提交 con.commit()
    finally:
        # 用完放回连接池
        cur.close()
        conn.close()

# 数据库链接
@contextmanager
def get_postgresql_cursor():
    con = POOL.getconn()
    con.set_client_encoding('utf-8')
    # 默认就自动提交
    con.autocommit = True
    try:
        yield con.cursor()
        # 或设置手动提交 con.commit()
    finally:
        # 用完放回连接池
        POOL.putconn(con)
'''

pool = PooledDB(creator=pymysql,maxconnections=6,mincached=2,maxcached=5,maxshared=3,blocking=True,maxusage=None,setsession=[],ping=1,host=Config.MYSQL_HOST,port=Config.MYSQL_PORT,user=Config.MYSQL_USER,password=Config.MYSQL_PW,
            database=Config.MYSQL_NAME,
            charset=Config.MYSQL_CHARSET
        )

@contextmanager
def get_cursor():
    conn = pool.connection()
    # 默认就自动提交
    conn.autocommit = True
    try:
        cur=conn.cursor()
        yield cur
        # 或设置手动提交 con.commit()
    finally:
        # 用完放回连接池
        cur.close()
        conn.close()

def test():
    sql = "select * from zimu_details"
    with get_cursor() as cursor:
        cursor.execute(sql)
        res = cursor.fetchall()
    return res[0]

def get_c1_data():
    sql = "select sum(confirm)," \
          "(select suspect from zimu_history order by ds desc limit 1)," \
          "sum(heal),sum(dead) from zimu_details " \
          "where update_time=(select update_time from zimu_details order by update_time desc limit 1) "
    with get_cursor() as cursor:
        cursor.execute(sql)
        res = cursor.fetchall()
    return res[0]

def get_c2_data():
    sql = "select province,sum(confirm) from zimu_details " \
          "where province!='' and update_time=(select update_time from zimu_details " \
          "order by update_time desc limit 1) " \
          "group by province"
    with get_cursor() as cursor:
        cursor.execute(sql)
        res = cursor.fetchall()
    return res

def get_l1_data():
    sql = "select ds,confirm,suspect,heal,dead from zimu_history order by ds"
    with get_cursor() as cursor:
        cursor.execute(sql)
        res = cursor.fetchall()
        return res

def get_l2_data():
    sql = "select ds,confirm_add,suspect_add from zimu_history order by ds"
    with get_cursor() as cursor:
        cursor.execute(sql)
        res = cursor.fetchall()
    return res

def get_r1_data():
    sql = "select city,sum_confirm from (select city,sum(confirm) as sum_confirm from zimu_details where update_time=(select update_time from zimu_details order by update_time desc limit 1) and city not in ('地区待确认') and province not in ('湖北','北京','上海','天津','重庆') group by city order by sum_confirm desc limit 5) as foo"
    with get_cursor() as cursor:
        cursor.execute(sql)
        res = cursor.fetchall()
    return res

def encrypt(signStr):
    hash_algorithm = hashlib.sha256()
    hash_algorithm.update(signStr.encode('utf-8'))
    return hash_algorithm.hexdigest()


def truncate(q):
    if q is None:
        return None
    size = len(q)
    return q if size <= 20 else q[0:10] + str(size) + q[size - 10:size]


def do_request(data):
    headers = {'Content-Type': 'application/x-www-form-urlencoded'}
    return requests.post(YOUDAO_URL, data=data, headers=headers)

def get_word_result(q):
    headers = {'Content-Type': 'application/x-www-form-urlencoded'}
    YOUDAO_URL = 'https://openapi.youdao.com/api'
    APP_KEY = '5acfdfea8658bee7'
    APP_SECRET = '6aN8whJWxGQHMtWvEBH4SdqgxWN9mOzE'
    data = {}
    data['from'] = 'en'
    data['to'] = 'zh-CHS'
    data['signType'] = 'v3'
    curtime = str(int(time.time()))
    data['curtime'] = curtime
    salt = str(uuid.uuid1())
    signStr = APP_KEY + truncate(q) + salt + curtime + APP_SECRET
    sign = encrypt(signStr)
    data['appKey'] = APP_KEY
    data['q'] = q
    data['salt'] = salt
    data['sign'] = sign
    response = requests.post(YOUDAO_URL, data=data, headers=headers)
    res=response.json()
    return res['translation'][0]

def get_country_translate():
    # geo_dict=get_country_list()
    # country_dict={get_word_result(key):key for key in geo_dict}
    filename = os.path.join(os.path.abspath(os.path.dirname(os.path.dirname(__file__))), 'static/geo/country_translate.json')
    #country_translate=json.dumps(country_dict)
    with open(filename) as f:
        country_translate=json.load(f)
    return country_translate

def get_country_list():
    filename=os.path.join(os.path.abspath(os.path.dirname(os.path.dirname(__file__))),'static/geo/world_geo.json')
    geo_dict={}
    with open(filename) as f:
        res=json.load(f)
        for geo in res['features']:
            geo_dict[geo['properties']['name']]=0
    return geo_dict